package com.database;
import java.sql.*;
import java.util.ArrayList;

import com.Helper.ReservationInfo;
import com.Helper.SeatInfo;

public class SeatDB extends Database
{
	private String seatID = null;
	private String flightID = null;
	private String status = null;
	private String seatType = null;
	
	public SeatDB()
	{
		tableName = "seat";
	}
	
	public SeatDB(SeatInfo info)
	{
		seatID = info.seatID;
		flightID = info.flightID;
		status = info.status;
		seatType = info.getType();
		tableName = "seat";
	}
    //@assignable statement,sqlString
    //@ensures CustomerDB db = new CustomerDB;
	//@signals_only RuntimeException, IOException,ServletException;
	public void Insert() throws SQLException 
	{
		Statement stmt = connection.createStatement();
		
	    String sqlString = "INSERT INTO " + tableName + " VALUES (" +
	    					AddQuotes(seatID) + ", " +
	    					AddQuotes(flightID) + ", " +
	    					AddQuotes(seatType) + ", " +
	    					AddQuotes(status) + 
	    					")";
	    System.out.println(sqlString);
	    stmt.executeUpdate(sqlString); 
	}
	 //@assignable statement,sqlString,ResultSet rs;
	//@signals_only RuntimeException, IOException,ServletException;
	public boolean IsReserved(String seat, String flight) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + tableName + " WHERE seatID = " + AddQuotes(seat)
				+ " and flightID = " + AddQuotes(flight);
		System.out.println(sqlString);
		ResultSet rs = stmt.executeQuery(sqlString);
		
		if (rs.next())//check the seat's availability
		{
			String s = rs.getString("seatStatus");
			
			if (s.equals("reserved"))
			{
				System.out.println("Seat is reserved");
				return true;
			}
			else
			{
				System.out.println("Seat is empty");
				return false;
			}
		}
		else
		{
			System.out.println("Seat does not exist");
			return false;
		}
	}
	 //@assignable statement stmt, sqlString
	//@signals_only RuntimeException, IOException,ServletException;
	public void MarkEmpty(String seat, String flight) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "UPDATE " + tableName + " SET seatStatus = 'empty' WHERE seatID = " + AddQuotes(seat) + 
							" AND flightID = " + AddQuotes(flight);
		System.out.println(sqlString);
		stmt.executeUpdate(sqlString);
		
	}
	 //@assignable statement stmt,sqlString;
	//@signals_only RuntimeException, IOException,ServletException;
	public void MarkReserved(String seat, String flight) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "UPDATE " + tableName + " SET seatStatus = 'reserved' WHERE seatID = " + AddQuotes(seat) + 
							" AND flightID = " + AddQuotes(flight);
		System.out.println(sqlString);
		stmt.executeUpdate(sqlString);
	}
	 //@assignable statement,sqlString
	 //@ensure result=info;
	//@signals_only RuntimeException, IOException,ServletException;
	public SeatInfo GetSelectedSeat(String seat, String flight) throws SQLException
	{
		SeatInfo info = new SeatInfo();
		
		String sqlString = "SELECT * FROM " + tableName + " where seatID = " + AddQuotes(seat) + " and flightID = " + AddQuotes(flight);
	    Statement stmt = connection.createStatement();

	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())//get seat info
	    {
	    	info.seatID = rs.getString("seatID");
	    	info.flightID = rs.getString("flightID");
	    	info.status = rs.getString("seatStatus");
	    	info.type = rs.getString("seatType");
	    }	
		
		return info;
	}
	 //@assignable ArrayList<SeatInfo>list, statement stmt,sqlString,ResultSet rs;
    //@ensures result=list;
	//@signals_only RuntimeException, IOException,ServletException;
	public ArrayList<SeatInfo> GetReservedSeat(String flightID) throws SQLException
	{
		ArrayList<SeatInfo> list = new ArrayList<SeatInfo>();
	    String sqlString = "SELECT * FROM " + tableName + " where seatStatus = 'reserved' and flightID = " + AddQuotes(flightID);
	    Statement stmt = connection.createStatement();

	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())//get new seat info and add it in database
	    {
			SeatInfo info = new SeatInfo();
	    	info.seatID = rs.getString("seatID");
	    	info.flightID = rs.getString("flightID");
	    	info.type = rs.getString("seatType");
	    	info.status = rs.getString("seatStatus");

	    	list.add(info);
	    }
		
		return list;
	}
	 //@assignable statement stmt,sqlString;
	//@signals_only RuntimeException, IOException,ServletException;
	public void Delete (String seat, String flight) throws SQLException
	{
		Statement stmt = connection.createStatement();
		
		String sqlString = "DELETE from " + tableName + " WHERE seatID = " + AddQuotes(seat)
							+ " and flightID = " + AddQuotes(flight);
		stmt.executeUpdate(sqlString);
	}
	 //@assignable statement stmt,sqlString resultSet rs;
	//@signals_only RuntimeException, IOException,ServletException;
	public void Display() throws SQLException 
	{
		String sqlString = "SELECT * FROM " + tableName;
	    Statement stmt = connection.createStatement();

	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())//display seatID and flightID
	    {
	        System.out.println
	        (
	            rs.getString("seatID") + " " +
	            rs.getString("flightID")
	        );
	    }	
	}

	@Override
	public void Delete(String primaryKey) throws SQLException 
	{
		System.out.println("use the other delete method");	
	}
}
